Grokking SQL for Tech Interviews
Rate this course
Ask Author
Contribute
Back to course home

1% completed

Vote For New Content
Introduction
Course Overview
What is RDBMS?
What is SQL?
A Quick Review of SQL
Read data from table
WHERE Clause
Operators
Handle NULLs in SQL
DISTINCT Clause
ORDER BY Clause
LIMIT and OFFSET
GROUP BY
HAVING
Aggregate Functions
Order of Execution
Order of SQL Query Execution
SQL Functions
Number Functions
String Functions
Date Functions
Nested Queries
Nested Query
Types of Subqueries
JOINS
Introduction
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
SELF JOIN
SET Operations
What are SET operations?
UNION
INTERSECT
EXCEPT
A Quick Review of DDL
Keys in SQL
CREATE
ALTER
DROP
TRUNCATE
Indexes
A Quick Review of DML
INSERT
UPDATE
DELETE
Database Backup & Restore
Backup Database
Restore Database
Interview Problems - Easy
1. Major Cities
2. Not boring movies
3. Triangle Judgement
4. Biggest Single Number
5. Product Sales Analysis I
6. Product Sales Analysis II
7. Game Play Analysis I
8. Game Play Analysis II
9. Queries Quality and Percentage
10. Average Selling Price
11. Weather Type in Each Country
12. Students and Examinations
13. Ads Performance
14. Group Sold Products By The Date
15. Employee Email Verification
16. Sellers With No Sales
17. Average Time of Process
18. Find Followers Count
19. Ad-Free Sessions
20. Bikes Last Time Used
21. Daily User Engagement Levels
22. Employee Attendance Record
23. Library Book Loans
24. Customer Purchase Summary
25. Long Comments (Easy)
26. Unused Accounts
27. Patient Appointment No-Shows
28. Books and Authors
29. High School Attendance
30. Employee Absences
31. Green Product Identification (Easy)
32. Employee Department Exclusion (Easy)
33. Sales Person (Easy)
34. Employee Task Tracking (Easy)
Interview Problems - Medium
1. Winning Candidate
2. Second Degree Follower
3. 2nd Highest Salary
4. Game Play Analysis III
5. Game Play Analysis IV
6. Active Businesses
7. Product Price on a Specific Date
8. Monthly Transactions II
9. Team Scores in Football Tournament
10. Page Recommendations
11. Running Total for Different Genders
12. Evaluate Boolean Expression
13. Active Users
14. Find Interview Candidates
15. Suspicious Bank Accounts
16. Order Fulfillment Rate (Medium)
17. Last Person to Fit in the Elevator (Medium)
18. Retail Expansion Analysis (Medium)
19. Employee Collaboration Networks (Medium)
20. Second Fastest Time in Races (Medium)
1. Trips and Users
2. Human Traffic of Stadium
3. Students Report By Geography
4. Game Play Analysis V
5. Number of Transactions per Visit
6. Find the Quiet Students in All Exams
7. First and Last Call On the Same Day
8. Build the Equation
9. Longest Winning Streak
10. Dynamic Pivoting of a Table
11. Dynamic Unpivoting of a Table
12. Generate the Invoice
13. Popularity Percentage
14. Viewers Turned Streamers
15. Status of Flight Tickets
1. High Performing Employees (Easy)
2. Student Course Averages (Easy)
3. Library Book Checkout Percentage (Easy)
4. Employee Performance and Improvement Rate (Easy)
5. Find Popular Posts (Easy)
6. Main Office Assignment for Each Employee (Easy)
7. Rectangle Validation (Easy)
8. Library Book Checkouts by Date (Easy)
9. Same-Day Appointment Fulfillment (Medium)
10. Departments with High Earning Employees (Medium)
11. Students Who Attended All Courses (Medium)
12. Library Book Checkouts First Year (Medium)
13. Repeated Values in Sequence (Medium)
14. School Top Achievers by Subject (Hard)
9. Team Scores in Football Tournament

Problem

Table: Teams

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
team_id is the column with unique values of this table.
Each row of this table represents a single football team.

Table: Matches

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| host_team     | int     |
| guest_team    | int     | 
| host_goals    | int     |
| guest_goals   | int     |
+---------------+---------+
match_id is the column of unique values of this table.
Each row is a record of a finished match between two different teams. 
Teams host_team and guest_team are represented by their IDs in the Teams table (team_id), and they scored host_goals and guest_goals goals, respectively.

Problem Definition

You have to compute the scores of all teams after all matches. Points are awarded as follows:

  • A team receives three points if they win a match (i.e., Scored more goals than the opponent team).
  • A team receives one point if they draw a match (i.e., Scored the same number of goals as the opponent team).
  • A team receives no points if they lose a match (i.e., Scored fewer goals than the opponent team).

Write a solution that selects the team_id, team_name and num_points of each team in the tournament after all described matches.

Return the result table ordered by num_points in decreasing order. In case of a tie, order the records by team_id in increasing order.

Example

Image

Output

Image

Try It Yourself

MYSQL

. . . .

Teams =

Matches =

Solution

To compute the scores of all teams after all matches, we need to determine the number of points each team has earned based on their performance in each match. Points are awarded as follows:

  • Three points for a win (scoring more goals than the opponent).
  • One point for a draw (scoring the same number of goals as the opponent).
  • No points for a loss (scoring fewer goals than the opponent).

The solution involves joining the Teams and Matches tables, calculating points per match for each team, aggregating these points, and finally ordering the results as specified.

Approach Overview

  • Join Teams with Matches: Combine the Teams and Matches tables to associate each team with their respective matches, whether they were the host or the guest team.
  • Calculate Points per Match: Use a CASE statement to assign points to each team based on the outcome of each match (win, draw, loss).
  • Aggregate Points per Team: Sum the points each team has earned across all matches.
  • Order the Results: Sort the final output by num_points in descending order. In case of a tie, sort by team_id in ascending order.

SQL Query

SELECT team_id, team_name, SUM(CASE WHEN team_id = host_team AND host_goals > guest_goals THEN 3 WHEN team_id = guest_team AND guest_goals > host_goals THEN 3 WHEN (team_id = host_team OR team_id = guest_team) AND host_goals = guest_goals THEN 1 ELSE 0 END) AS num_points FROM Teams T LEFT JOIN Matches M ON T.team_id = M.host_team OR T.team_id = M.guest_team GROUP BY team_id, team_name ORDER BY num_points DESC, team_id ASC;

Step-by-Step Approach

Step 1: Join Teams with Matches

Associate each team with all the matches they have participated in, whether as the host or guest team. This allows us to evaluate each team's performance in every match.

SQL Query:

SELECT T.team_id, T.team_name, M.host_team, M.guest_team, M.host_goals, M.guest_goals FROM Teams T LEFT JOIN Matches M ON T.team_id = M.host_team OR T.team_id = M.guest_team;

Explanation:

  • SELECT T.team_id, T.team_name, M.host_team, M.guest_team, M.host_goals, M.guest_goals:
    • Retrieves the team ID and name from the Teams table and the corresponding match details from the Matches table.

  • LEFT JOIN Matches M ON T.team_id = M.host_team OR T.team_id = M.guest_team:
    • Performs a left join to include all teams, whether they have participated in matches or not.
    • Associates each team with matches where they are either the host or guest team.

Output After Step 1:

+---------+---------------+-----------+------------+-------------+-------------+ | team_id | team_name | host_team | guest_team | host_goals | guest_goals | +---------+---------------+-----------+------------+-------------+-------------+ | 10 | DesignGuru FC | 10 | 20 | 3 | 0 | | 10 | DesignGuru FC | 30 | 10 | 2 | 2 | | 10 | DesignGuru FC | 10 | 50 | 5 | 1 | | 20 | NewYork FC | 20 | 30 | 1 | 0 | | 20 | NewYork FC | 20 | 30 | 1 | 0 | | 30 | Atlanta FC | 30 | 10 | 2 | 2 | | 30 | Atlanta FC | 20 | 30 | 1 | 0 | | 30 | Atlanta FC | 50 | 30 | 1 | 0 | | 40 | Chicago FC | NULL | NULL | NULL | NULL | | 50 | Toranto FC | 50 | 30 | 1 | 0 | | 50 | Toranto FC | 10 | 50 | 5 | 1 | +---------+---------------+-----------+------------+-------------+-------------+

Step 2: Calculate Points per Match

Assign points to each team based on the outcome of each match they participated in. The CASE statement evaluates whether the team won, drew, or lost the match and assigns points accordingly.

SQL Query:

SELECT T.team_id, T.team_name, CASE WHEN T.team_id = M.host_team AND M.host_goals > M.guest_goals THEN 3 WHEN T.team_id = M.guest_team AND M.guest_goals > M.host_goals THEN 3 WHEN (T.team_id = M.host_team OR T.team_id = M.guest_team) AND M.host_goals = M.guest_goals THEN 1 ELSE 0 END AS points FROM Teams T LEFT JOIN Matches M ON T.team_id = M.host_team OR T.team_id = M.guest_team;

Explanation:

  • CASE Statement:
    • WHEN T.team_id = M.host_team AND M.host_goals > M.guest_goals THEN 3:
      • If the team is the host and scores more goals than the guest, they win and earn 3 points.
    • WHEN T.team_id = M.guest_team AND M.guest_goals > M.host_goals THEN 3:
      • If the team is the guest and scores more goals than the host, they win and earn 3 points.
    • WHEN (T.team_id = M.host_team OR T.team_id = M.guest_team) AND M.host_goals = M.guest_goals THEN 1:
      • If the team is either host or guest and the match is a draw (same number of goals), they earn 1 point.
    • ELSE 0:
      • In all other cases (i.e., the team loses the match), they earn 0 points.
  • AS points:
    • Aliases the calculated points for clarity.

Output After Step 2:

+---------+---------------+--------+ | team_id | team_name | points | +---------+---------------+--------+ | 10 | DesignGuru FC | 3 | | 10 | DesignGuru FC | 1 | | 10 | DesignGuru FC | 3 | | 20 | NewYork FC | 3 | | 20 | NewYork FC | 0 | | 30 | Atlanta FC | 0 | | 30 | Atlanta FC | 0 | | 30 | Atlanta FC | 1 | | 40 | Chicago FC | 0 | | 50 | Toranto FC | 3 | | 50 | Toranto FC | 0 | +---------+---------------+--------+

Step 3: Aggregate Points per Team

Sum the points each team has earned across all their matches to determine their total score in the tournament.

SQL Query:

SELECT team_id, team_name, SUM(CASE WHEN team_id = host_team AND host_goals > guest_goals THEN 3 WHEN team_id = guest_team AND guest_goals > host_goals THEN 3 WHEN (team_id = host_team OR team_id = guest_team) AND host_goals = guest_goals THEN 1 ELSE 0 END) AS num_points FROM Teams T LEFT JOIN Matches M ON T.team_id = M.host_team OR T.team_id = M.guest_team GROUP BY team_id, team_name;

Explanation:

  • SUM(CASE ... END) AS num_points:
    • Sums the points each team has earned from all matches.
  • GROUP BY team_id, team_name:
    • Groups the results by each team to aggregate their points.

Output After Step 3:

+---------+---------------+------------+ | team_id | team_name | num_points | +---------+---------------+------------+ | 10 | DesignGuru FC | 7 | | 20 | NewYork FC | 3 | | 50 | Toranto FC | 3 | | 30 | Atlanta FC | 1 | | 40 | Chicago FC | 0 | +---------+---------------+------------+

Step 4: Order the Results

Sort the teams based on their total points in descending order. In case of a tie in points, sort the teams by their team_id in ascending order to maintain consistency.

SQL Query:

SELECT team_id, team_name, SUM(CASE WHEN team_id = host_team AND host_goals > guest_goals THEN 3 WHEN team_id = guest_team AND guest_goals > host_goals THEN 3 WHEN (team_id = host_team OR team_id = guest_team) AND host_goals = guest_goals THEN 1 ELSE 0 END) AS num_points FROM Teams T LEFT JOIN Matches M ON T.team_id = M.host_team OR T.team_id = M.guest_team GROUP BY team_id, team_name ORDER BY num_points DESC, team_id ASC;

Explanation:

  • ORDER BY num_points DESC, team_id ASC:
    • num_points DESC:
      • Sorts the teams by their total points in descending order, so teams with higher points appear first.
    • team_id ASC:
      • In case two or more teams have the same number of points, they are sorted by their team_id in ascending order to break the tie.

Final Output:

+---------+---------------+------------+ | team_id | team_name | num_points | +---------+---------------+------------+ | 10 | DesignGuru FC | 7 | | 20 | NewYork FC | 3 | | 50 | Toranto FC | 3 | | 30 | Atlanta FC | 1 | | 40 | Chicago FC | 0 | +---------+---------------+------------+
8. Monthly Transactions II
10. Page Recommendations
Mark as Completed

On this page

Problem

Problem Definition

Example

Output

Try It Yourself

Solution

Approach Overview

SQL Query

Step-by-Step Approach